﻿------------------------------------
-- Insert test data.
--
--
USE wcfSyncSamplesDb
GO

--Wrap the inserts in a procedure so that each snippet
--can call the procedure to reset the database after
--the snippet completes.
CREATE PROCEDURE usp_InsertSampleData

AS

	SET NOCOUNT ON

	DELETE FROM Sales.CustomerContact
	DELETE FROM Sales.Customer
	
	--Insert into Customer.
	INSERT INTO Sales.Customer (CustomerName, SalesPerson, CustomerType) VALUES (N'Aerobic Exercise Company', N'James Bailey', N'Wholesale')
	INSERT INTO Sales.Customer (CustomerName, SalesPerson, CustomerType) VALUES (N'Exemplary Cycles', N'James Bailey', N'Retail')
	INSERT INTO Sales.Customer (CustomerName, SalesPerson, CustomerType) VALUES (N'Tandem Bicycle Store', N'Brenda Diaz', N'Wholesale')
	INSERT INTO Sales.Customer (CustomerName, SalesPerson, CustomerType) VALUES (N'Rural Cycle Emporium', N'Brenda Diaz', N'Retail')
	INSERT INTO Sales.Customer (CustomerName, SalesPerson, CustomerType) VALUES (N'Sharp Bikes', N'Brenda Diaz', N'Retail')

	--Declare variables that are used in subsequent inserts.
	DECLARE @CustomerId uniqueidentifier
	DECLARE @OrderId uniqueidentifier
	DECLARE @InsertString nvarchar(1024)

	----------------------------------
	-------- First Customer ----------
	----------------------------------
	--No additional inserts for Aerobic Exercise Company


	----------------------------------
	------- Second Customer ----------
	----------------------------------
	--Insert into CustomerContact.
	SELECT @CustomerId = CustomerId FROM Sales.Customer WHERE CustomerName = N'Exemplary Cycles'
	SET @InsertString = 'INSERT INTO Sales.CustomerContact (CustomerId, PhoneNumber, PhoneType) VALUES (''' + CAST(@CustomerId AS nvarchar(38)) + ''', ''959-555-0151'', ''Business'')'
	EXECUTE sp_executesql @InsertString

	----------------------------------
	-------- Third Customer ----------
	----------------------------------
	--Insert into CustomerContact.
	SELECT @CustomerId = CustomerId FROM Sales.Customer WHERE CustomerName = N'Tandem Bicycle Store'
	SET @InsertString = 'INSERT INTO Sales.CustomerContact (CustomerId, PhoneNumber, PhoneType) VALUES (''' + CAST(@CustomerId AS nvarchar(38)) + ''', ''107-555-0138'', ''Business'')'
	EXECUTE sp_executesql @InsertString


	----------------------------------
	------- Fourth Customer ----------
	----------------------------------
	--Insert into CustomerContact.
	SELECT @CustomerId = CustomerId FROM Sales.Customer WHERE CustomerName = N'Rural Cycle Emporium'
	SET @InsertString = 'INSERT INTO Sales.CustomerContact (CustomerId, PhoneNumber, PhoneType) VALUES (''' + CAST(@CustomerId AS nvarchar(38)) + ''', ''158-555-0142'', ''Business'')'
	EXECUTE sp_executesql @InsertString

	--Insert into CustomerContact (second contact info).
	SELECT @CustomerId = CustomerId FROM Sales.Customer WHERE CustomerName = N'Rural Cycle Emporium'
	SET @InsertString = 'INSERT INTO Sales.CustomerContact (CustomerId, PhoneNumber, PhoneType) VALUES (''' + CAST(@CustomerId AS nvarchar(38)) + ''', ''453-555-0167'', ''Mobile'')'
	EXECUTE sp_executesql @InsertString


	SET NOCOUNT OFF

GO -- End of usp_InsertSampleData

EXEC usp_InsertSampleData
